'use strict';

const Controller = require('egg').Controller;

class ReportController extends Controller {
  async index() {
    this.ctx.body = 'hi, egg';
  }
  async getDayReport() {
    const { db } = this.ctx.app;
    let query = this.ctx.query;
    let res = {}, sql
    let fkCount = await db.all(`select count (*) as count from visitor`)
    let jcCount = await db.all(`select count (*) as count from inout`)
    let fkDay = await db.all(`select count (*) as count from visitor where time>= '${query.startTime}' and time<= '${query.endTime}'`)
    let jcDay = await db.all(`select count (*) as count from inout where time>= '${query.startTime}' and time<= '${query.endTime}'`)
    let xsCount = await db.all(`select count (*) as count from people where type=1`)
    let lsCount = await db.all(`select count (*) as count from inout where type=2`)
    sql = `select a.number,a.fks,b.jcs,s.name from (select number,count(1) as fks from visitor  group by number )a left join 
      (select number,count(1) as jcs from inout  group by number) b on  a.number=b.number inner join  school  s on a.number=s.number `
    // if (query.startTime && query.endTime) {
    //   sql = `select a.number,a.fks,b.jcs,s.name from (select number,count(1) as fks from visitor 
    //   where time>'${query.startTime}'  and time<'${query.endTime}' group by number )a left join 
    //   (select number,count(1) as jcs from inout where time>'${query.startTime}'  and time<'${query.endTime}' group by number) b on  a.number=b.number inner join  school  s on a.number=s.number `
    // } else {
    //   sql = `select a.number,a.fks,b.jcs,s.name from (select number,count(1) as fks from visitor  group by number )a left join 
    //   (select number,count(1) as jcs from inout  group by number) b on  a.number=b.number inner join  school  s on a.number=s.number `
    // }
    let echartCount = await db.all(sql)
    res.result = {
      fkCount: fkCount[0].count,
      jcCount: jcCount[0].count,
      fkDay: fkDay[0].count,
      jcDay: jcDay[0].count,
      xsCount: xsCount[0].count,
      lsCount: lsCount[0].count,
      echartCount: echartCount
    }
    this.ctx.body = res
  }

  async  getYearReport() {
    const { db } = this.ctx.app;
    let query = this.ctx.query;
    let nextYear = (query.years * 1) + 1;
    console.log(query.years, nextYear)
    let sql = `select a.number,a.month,a.fks,b.jcs,s.name from (select number,strftime('%m',time) as month,count(1) as fks from visitor where time  between '${query.years}-01-01'  and '${nextYear}-01-01' group by number,
    strftime('%Y',time)) a left join (select number,strftime('%m',time) as month,count(1) as jcs from inout where time  between '${query.years}-01-01'  and '${nextYear}-01-01' group by number,strftime('%Y',time)) b on a.month=b.month and a.number=b.number
    inner join  school  s on a.number=s.number  `
    let result = await db.all(sql)
    this.ctx.body = result
  }

  async getRangeCount() {
    const { db } = this.ctx.app;
    let query = this.ctx.query;
    let res = {}, sql;
    if (query.startTime && query.endTime) {
      sql = `select a.number,a.fks,b.jcs,s.name from (select number,count(1) as fks from visitor 
      where time>'${query.startTime}'  and time<'${query.endTime}' group by number )a left join 
      (select number,count(1) as jcs from inout where time>'${query.startTime}'  and time<'${query.endTime}' group by number) b on  a.number=b.number inner join  school  s on a.number=s.number `
    } else {
      sql = `select a.number,a.fks,b.jcs,s.name from (select number,count(1) as fks from visitor  group by number )a left join 
      (select number,count(1) as jcs from inout  group by number) b on  a.number=b.number inner join  school  s on a.number=s.number `
    }
    res.result = await db.all(sql)
    this.ctx.body = res
  }

}

module.exports = ReportController;
